
package sql;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

public class SQLiteUtilityManagement {
	public final String lunPerTimeUnitTable = "LunPerTimeUnit";
	public final String lunTable = "Lun";
	public final String timeUnitTable = "TimeUnit";
	private final int defaultLunSize = 10,  TimeUnitLength = 10;
	//static final String filename = "database.db";
	//static final String url = "jdbc:sqlite:"+filename;
	private String _url; //"jdbc:sqlite:"+filename
	private String _filename;
	private Connection _con;

	public SQLiteUtilityManagement(String filename) {
		try {
			Class.forName("org.sqlite.JDBC");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		_filename = filename;
		_url = "jdbc:sqlite:" + _filename;
		openConn();
	}

	private void openConn() {
		try {
			_con = DriverManager.getConnection(_url);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public ResultSet executeQuery(String query) {
		try {
			Statement stmt = _con.createStatement();
			return stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public void executeUpdate(String query) {
		try {
			Statement stmt = _con.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void close() {
		try {
			_con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void createTables(){
		executeUpdate("CREATE TABLE lun(" +
				"id int PRIMARY KEY," +
				"size int);");
		executeUpdate("CREATE TABLE timeunit(" +
				"id int PRIMARY KEY," +
				"size numeric);");
		executeUpdate("CREATE TABLE LUNPerTimeUnit(" +
				"lun_id int REFERENCES lun(id)," +
				"timeunit_id int REFERENCES timeunit(id)," +
				"kbWritten double," +
				"kbRead double," +
				"numOfReads double," +
				"numOfWrites double," +
				"PRIMARY KEY(lun_id, timeunit_id));");// +
		//                    "ReadCacheHitsPrecentage int, WriteCacheHitsPrecentage int)");
	}

	/*
	 *filename is the name of the data base
	 *initialize the data base according to some default values
	 * deletes previous data base!!!!
	 */
	public void createDefaultDatabase() {
		try {
			close(); //so i will be able to delete _filename
			File f = new File(_filename);
			f.delete();
			f.createNewFile();
			SQLiteUtilityManagement db = new SQLiteUtilityManagement(_filename);
			String strStart = "INSERT INTO "+lunPerTimeUnitTable+" VALUES(";
			/*db.executeUpdate(strStart + "0,0,10,10,15,15,20,20);");
             db.executeUpdate(strStart + "0,1,10,10,15,15,20,20);");
             db.executeUpdate(strStart + "1,0,20,20,25,25,30,30);");
             db.executeUpdate(strStart + "1,1,20,20,25,25,30,30);");*/
			db.executeUpdate(strStart + "0,0,10,10,15,15);");
			db.executeUpdate(strStart + "0,1,10,10,15,15);");
			db.executeUpdate(strStart + "1,0,20,20,25,25);");
			db.executeUpdate(strStart + "1,1,20,20,25,25);");
			db.createLunAndTimeUnitTables();
			db.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		openConn();
	}

	private void createLunAndTimeUnitTables() {
		//TODO fix this, there is no start time and end time for timeunit. there is size.
		Vector<String> insertQuerys = new Vector<String>();
		ResultSet lun_ids, timeUnit_ids;

		try {
			lun_ids = executeQuery("Select distinct lun_id from " + lunPerTimeUnitTable);
			String lun_id, timeUnit_id;
			while (lun_ids.next()) {
				lun_id = lun_ids.getString("lun_id");
				insertQuerys.add("Insert into " + lunTable + " Values( " + lun_id + " ,  " + defaultLunSize + ")");

			}
			lun_ids.close();
			Calendar startTime, endTime;
			startTime = Calendar.getInstance();
			startTime.setTimeInMillis(1242824040016L);//a random time (to make sure each time the database is created it's the same)
			endTime = (Calendar) startTime.clone();
			endTime.add(Calendar.SECOND, TimeUnitLength);
			timeUnit_ids = executeQuery("Select distinct timeunit_id from " + lunPerTimeUnitTable);
			while (timeUnit_ids.next()) {
				timeUnit_id = timeUnit_ids.getString("timeunit_id");
				insertQuerys.add("Insert into " + timeUnitTable + "  Values( " + timeUnit_id + " , " + "datetime(" + ((startTime.getTimeInMillis()) / 1000) +
						",'unixepoch') , datetime( " + ((endTime.getTimeInMillis()) / 1000) + ",'unixepoch') " + ")");
				startTime = (Calendar) endTime.clone();
				endTime = (Calendar) startTime.clone();
				endTime.add(Calendar.SECOND, TimeUnitLength);
			}
			for (int i = 0; i < insertQuerys.size(); i++) {
				executeUpdate(insertQuerys.get(i));
			}
		} catch (SQLException ex) {
			Logger.getLogger(SQLiteUtilityManagement.class.getName()).log(Level.SEVERE, null, ex);
			System.out.println("creation of default database failed");
		}
	}

	public static void main(String[] args) {
		//SQLiteUtilityManagement db = new SQLiteUtilityManagement("testDB.db");
		SQLiteUtilityManagement db = new SQLiteUtilityManagement("database.db");
		db.createDefaultDatabase();
	}

	public void executeTransaction(Vector<String> statements) {
//		int index = 0;
		for (String s: statements){
//			System.out.println(index + " - " + s);
//			index++;
			executeUpdate(s);
		}

	}
}
